package db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

public class LogMethods {
	
	private String myUsername = "mark";
	
	//insert initialization for myUserName here
	
	private String getDate(){
	      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	      Date date = new Date();

	      return dateFormat.format(date);
	}	
	
	public void AddLog(Asset myAsset)
	{
		Connection con = DBConnectionFactory.getInstance().getConnection();
		
		String query = "INSERT into addlog(datetime, username, id, name, owner, custodian, type, dateAcquired, retentionPeriod, maintenanceSchedule, financial, confidentiality, integrity, availability, classification, storageLocation)" +
				"values(\""+getDate()+"\", \""+myUsername+"\", \""+ myAsset.getId() +"\", \""+myAsset.getName()+"\", \""+myAsset.getOwner()+"\", \""+myAsset.getCustodian()+"\", \""+myAsset.getTypeCode()+"\", \""+myAsset.getDateAcquired()+  
				"\", \""+myAsset.getRetentionPeriod()+"\", \""+myAsset.getMaintenanceScheduleCode()+"\", \""+myAsset.getFinancialCode()+"\", \""+myAsset.getConfidentialityCode()+"\", \""+myAsset.getIntegrityCode()+"\", \""+myAsset.getAvailabilityCode()+
				"\", \""+myAsset.getClassificationCode()+"\", \""+myAsset.getStorageLocation()+"\")";
		
		DBConnector.executeUpdate(con, query);
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void EditLog(Asset myAsset)
	{
		//System.out.println("entered");
		Connection con = DBConnectionFactory.getInstance().getConnection();
		String query = "INSERT into editlog(datetime, username, id, name, owner, custodian, type, dateAcquired, retentionPeriod, maintenanceSchedule, financial, confidentiality, integrity, availability, classification, storageLocation)" +
				"values(\""+getDate()+"\", \""+myUsername+"\", \""+myAsset.getId()+"\", \""+ myAsset.getName() + "\", \""+myAsset.getOwner()+"\", \""+myAsset.getCustodian()+"\", \""+myAsset.getTypeCode()+"\", \""+myAsset.getDateAcquired()+
				"\", \""+myAsset.getRetentionPeriod()+"\", \""+myAsset.getMaintenanceScheduleCode()+"\", \""+myAsset.getFinancialCode()+"\", \""+myAsset.getConfidentialityCode()+"\", \""+myAsset.getIntegrityCode()+"\", \""+myAsset.getAvailabilityCode()+
				"\", \""+myAsset.getClassificationCode()+"\", \""+myAsset.getStorageLocation()+"\")";
		
		DBConnector.executeUpdate(con, query);
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void DeleteLog(Asset myAsset)
	{
		Connection con = DBConnectionFactory.getInstance().getConnection();
		String query = "INSERT into deletelog(datetime, username, id, name)" +
				"values(\""+getDate()+"\", \""+myUsername+"\", \""+myAsset.getId()+"\", \""+myAsset.getName()+"\")";
		
		DBConnector.executeUpdate(con, query);
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public TableModel displayTable(ResultSet rs){
		try {
            ResultSetMetaData metaData = rs.getMetaData();
            int numberOfColumns = metaData.getColumnCount();
            Vector columnNames = new Vector();

            // Get the column names
            for (int column = 0; column < numberOfColumns; column++) {
                columnNames.addElement(metaData.getColumnLabel(column + 1));
                }

            // Get all rows.
            Vector rows = new Vector();

            while (rs.next()) {
                Vector newRow = new Vector();
                for (int i = 1; i <= numberOfColumns; i++) {
                    newRow.addElement(rs.getObject(i));
                }
                rows.addElement(newRow);
                }

                return new DefaultTableModel(rows, columnNames);
            } catch (Exception e) {
                System.out.println("Failed to connect");
                return null;
                }	
	}
	
	public void ViewLog(JTable table)
	{
		Connection con = DBConnectionFactory.getInstance().getConnection();
		String query = "Select datetime AS DateTime , ID AS AssetID, name AS AssetName, username AS Username, TypeOfAction from( select datetime, ID, name, username, 'Add' TypeOfAction from addlog union all select datetime, ID, name, username,  'Edit' TypeOfAction from editlog union all select datetime, ID, name, username, 'Delete' TypeOfAction from deletelog ) X order by datetime desc";
		
		ResultSet rs = DBConnector.executeQuery(con, query);
		
		table.setModel(displayTable(rs));
		
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void ViewLogByID(JTable table, int selectedID)
	{
		Connection con = DBConnectionFactory.getInstance().getConnection();
		String query = "Select datetime AS DateTime, ID As AssetID, name AS AssetName, username AS Username, TypeOfAction from( select datetime, ID, name, username, 'Add' TypeOfAction from addlog union all select datetime, ID, name, username, 'Edit' TypeOfAction from editlog union all select datetime, ID, name, username, 'Delete' TypeOfAction from deletelog ) X where ID = \""+selectedID+"\" order by datetime desc";
		
		ResultSet rs = DBConnector.executeQuery(con, query);
		
		table.setModel(displayTable(rs));
		
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
